using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Sfd.Entities;

namespace Sfd.Repositories.SqlClient
{
    public class ProjectRepository : RepositoryBase, IProjectRepository
    {
        private const string SqlUnpublish = "DELETE FROM Projects WHERE Id=@Id";
        private const string SqlIsPublished = "SELECT Count(*) FROM Projects Where Id=@Id";
        private const string SqlPublish = @"INSERT INTO Projects
                   ([Id]
                   ,[FriendlyName]
                   ,[AccountName]
                   ,[SharedKey]
                   ,[ContainerName])
             VALUES
                   (@Id
                   ,@FriendlyName
                   ,@AccountName
                   ,@SharedKey
                   ,@ContainerName)";

        private const string SqlAddUserInProject =
            @"INSERT INTO UsersInProjects
                   ([UserId]
                   ,[ProjectId])
             VALUES
                   (@UserId
                   ,@ProjectId)";

        private const string SqlRemoveUserFromProject =
            @"DELETE FROM UsersInProjects WHERE UserId=@UserID AND ProjectId = @ProjectId";

        private const string SqlRemoveAllUsersFromProject =
            @"DELETE FROM UsersInProjects WHERE ProjectId = @ProjectId";

        private const string SqlIsUserInProject = @"SELECT Count(*) FROM UsersInProjects WHERE UserId = @UserId AND ProjectId = @ProjectId";

        private const string SqlGetUserProjects =
            @"SELECT 
                [Id]
                ,[FriendlyName]
                ,[AccountName]
                ,[SharedKey]
                ,[ContainerName]
            FROM Projects
            INNER JOIN UsersInProjects uip on uip.ProjectId = Projects.Id
            WHERE uip.UserId = @UserId";

        private const string SqlLoadAll =
            @"SELECT 
                [Id]
                ,[FriendlyName]
                ,[AccountName]
                ,[SharedKey]
                ,[ContainerName]
            FROM Projects";

        private const string SqlLoadById =
            @"SELECT 
                [Id]
                ,[FriendlyName]
                ,[AccountName]
                ,[SharedKey]
                ,[ContainerName]
            FROM Projects
            WHERE Id = @ProjectId";

        public int PublishProject(AzureSyncProject project)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                using (var command = new SqlCommand(SqlPublish, connection))
                {
                    command.Parameters.AddWithValue("@Id", project.Id);
                    command.Parameters.AddWithValue("@FriendlyName", project.FriendlyName);
                    command.Parameters.AddWithValue("@AccountName", project.AccountName);
                    command.Parameters.AddWithValue("@SharedKey", project.SharedKey);
                    command.Parameters.AddWithValue("@ContainerName", project.ContainerName);
                    
                    connection.Open();
                    return command.ExecuteNonQuery();
                }
            }
        }

        public int IsPublished(Guid projectId)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                using (var command = new SqlCommand(SqlIsPublished, connection))
                {
                    command.Parameters.AddWithValue("@Id", projectId);
                    connection.Open();
                    return (int) command.ExecuteScalar();
                }
            }
        }

        public int Unpublish(Guid projectId)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                using (var command = new SqlCommand(SqlUnpublish, connection))
                {
                    command.Parameters.AddWithValue("@Id", projectId);
                    connection.Open();
                    return command.ExecuteNonQuery();
                }
            }
        }

        public int AddUserInProject(Guid userId, Guid projectId)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                using (var command = new SqlCommand(SqlAddUserInProject, connection))
                {
                    command.Parameters.AddWithValue("@ProjectId", projectId);
                    command.Parameters.AddWithValue("@UserId", userId);

                    connection.Open();
                    return command.ExecuteNonQuery();
                }
            }
        }

        public int RemoveAllUsersFromProject(Guid projectId)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                using (var command = new SqlCommand(SqlRemoveAllUsersFromProject, connection))
                {
                    command.Parameters.AddWithValue("@ProjectId", projectId);

                    connection.Open();
                    return command.ExecuteNonQuery();
                }
            }
        }

        public int RemoveUserFromProject(Guid userId, Guid projectId)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                using (var command = new SqlCommand(SqlRemoveUserFromProject, connection))
                {
                    command.Parameters.AddWithValue("@ProjectId", projectId);
                    command.Parameters.AddWithValue("@UserId", userId);

                    connection.Open();
                    return command.ExecuteNonQuery();
                }
            }
        }

        public int IsUserInProject(Guid userId, Guid projectId)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                using (var command = new SqlCommand(SqlIsUserInProject, connection))
                {
                    command.Parameters.AddWithValue("@UserId", userId);
                    command.Parameters.AddWithValue("@ProjectId", projectId);
                    connection.Open();
                    return (int)command.ExecuteScalar();
                }
            }
        }

        public IEnumerable<AzureSyncProject> GetUserProjects(Guid userId)
        {
            var items = new List<AzureSyncProject>();
            using (var connection = new SqlConnection(ConnectionString))
            {
                using (var command = new SqlCommand(SqlGetUserProjects, connection))
                {
                    command.Parameters.AddWithValue("@UserId", userId);
                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                            items.Add(GetFromReader(reader));
                    }
                }
            }
            return items;
        }

        public IEnumerable<AzureSyncProject> LoadAll()
        {
            var items = new List<AzureSyncProject>();
            using (var connection = new SqlConnection(ConnectionString))
            {
                using (var command = new SqlCommand(SqlLoadAll, connection))
                {
                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                            items.Add(GetFromReader(reader));
                    }
                }
            }
            return items;
        }

        public AzureSyncProject LoadById(Guid projectId)
        {
            AzureSyncProject item = null;
            using (var connection = new SqlConnection(ConnectionString))
            {
                using (var command = new SqlCommand(SqlLoadById, connection))
                {
                    command.Parameters.AddWithValue("@ProjectId", projectId);
                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                            item = GetFromReader(reader);
                    }
                }
            }
            return item;
        }

        private static AzureSyncProject GetFromReader(IDataReader reader)
        {
            var item = new AzureSyncProject();
            item.Id = (Guid) reader["Id"];
            item.FriendlyName = (string) reader["FriendlyName"];
            item.AccountName = (string) reader["AccountName"];
            item.SharedKey = (string) reader["SharedKey"];
            item.ContainerName = (string) reader["ContainerName"];

            return item;
        }
    }
}